!pip install contextily
import sys
sys.path.insert(0, 'lib')
import geopandas as gpd
import geojson as gjs
from geopandas import GeoDataFrame
from shapely.geometry import Point, LineString
import pandas as pd
import matplotlib.pyplot as plt
import json
#import contextily as ctx
import re
import numpy as np
from plotly.offline import iplot
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.graph_objects as go
from google.cloud import bigquery
from google.oauth2 import service_account
#from google.cloud import storage
%load_ext google.cloud.bigquery
#----Traigo el query completo desde Big Query-------
credencial= service_account.Credentials.from_service_account_file(
'C:/Users/castr/D4SA/Final Project/secretaria-movilidad-d7884405beb2.json',
scopes=["https://www.googleapis.com/auth/cloud-platform"]
)
client= bigquery.Client(
credentials= credencial,
project=credencial.project_id,
)
query ='''
SELECT * FROM `secretaria-movilidad.bitcarrier.bitcarrier`
WHERE fecha >'2020-07-31'
'''
prueba = client.query(query)
df_bitcarrier=prueba.to_dataframe()
'''
df_bitcarrier_ago = gpd.read_file('/content/drive/My Drive/Team 80 DS4A/Secretaría de Movilidad - SGV/Data_Final/Bitcarrier_5min/velAgosto2020_24h.csv')
df_bitcarrier_sep = gpd.read_file('/content/drive/My Drive/Team 80 DS4A/Secretaría de Movilidad - SGV/Data_Final/Bitcarrier_5min/velseptiembre2020_24h.csv')
df_bitcarrier_oct = gpd.read_file('/content/drive/My Drive/Team 80 DS4A/Secretaría de Movilidad - SGV/Data_Final/Bitcarrier_5min/velOctubre2020_24h.csv')
df_bitcarrier = df_bitcarrier_ago.append([df_bitcarrier_sep, df_bitcarrier_oct]).reset_index()
'''
#Converting types
df_bitcarrier['tid'] = df_bitcarrier['tid'].astype('int64')
df_bitcarrier['speed'] = df_bitcarrier['speed'].astype('float64')
df_bitcarrier['count'] = df_bitcarrier['count'].astype('int')
#Converting time to Date Time type
df_bitcarrier['Datetime'] = pd.to_datetime(df_bitcarrier['fecha'])
display(df_bitcarrier.info())
print(print(df_bitcarrier.isnull().values.any()))
df_bitcarrier.head()
#Read key file
llave_movilidad = pd.read_excel('C:/Users/castr/D4SA/Final Project/TramosCGT-REV.dbf - original.xlsx')
print(llave_movilidad.info())
llave_movilidad